package weaver.interfaces.expense;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.Map;
import java.util.Set;

import weaver.conn.RecordSet;
import weaver.conn.RecordSetDataSource;
import weaver.formmode.setup.ModeRightInfo;
import weaver.general.BaseBean;
import weaver.general.Util;
import weaver.interfaces.util.BigDecimalCalculate;
import weaver.interfaces.wjutil.JDBCUtils;

/**
 * 同步预算台账
 * 
 * @author wangjiepc
 *
 */
public class SynYstz {

	// 预算台账表
	private static String ysxxtable = (new BaseBean()).getPropValue("expense", "ysxxtable");
	
	// 数据库连接地址
	private static String databaseUrl = (new BaseBean()).getPropValue("db", "databaseUrl");
	// 数据库连接端口
	private static String databasePort = (new BaseBean()).getPropValue("db", "databasePort");
	// 数据库名称
	private static String databaseName = (new BaseBean()).getPropValue("db", "databaseName");
	// 数据库用户名密码
	private static String databaseUsername = (new BaseBean()).getPropValue("db", "databaseUsername");
	private static String databasePassword = (new BaseBean()).getPropValue("db", "databasePassword");

	public String execute() throws Exception {

		// 初始化JDBC工具类
		JDBCUtils jdbcutil = new JDBCUtils(
				"jdbc:sqlserver://" + databaseUrl + ":" + databasePort + ";databaseName="+databaseName, databaseUsername, databasePassword)
						.init();

		// 获取连接
		Connection ct = jdbcutil.getConn();

		// 获取视图连接
		RecordSetDataSource rs = new RecordSetDataSource("ZJK");

		// 查询视图中全部预算台账
		String sql = "select *  from EF_EP_V_EPBudget";

		// SQL map集合
		ArrayList<Map<String, String>> sqlMaps = sql2map(sql);

		// 日志
		StringBuffer log = new StringBuffer();

		// SQL集合
		ArrayList<String> sqls = new ArrayList<String>();

		int itemcount = 0;
		
		RecordSet rt = new RecordSet();
		for (Map<String, String> map : sqlMaps) {
			// 查询该预算是否已经存在
			String sqlWhere = " iyear ='" + map.get("iyear") + "" + "' and cbudgettype='" + map.get("cbudgettype") + ""
					+ "' and centitycode='" + map.get("centitycode") + "" + "' and cexpenseitemcode='"
					+ map.get("cexpenseitemcode") + "'";
			//判断是否是项目
			if(!"".equals(map.get("citemcode"))){
				//是 添加项目条件
				sqlWhere += " and citemcode = '"+map.get("citemcode")+"'";
				itemcount++;
			}
			
			new BaseBean().writeLog(sqlWhere + "^--^"+"=========是否项目citemcode:"+map.get("citemcode"));

			rt.execute("select id,dBBGAmount,dcurrentamount from " + ysxxtable + " where " + sqlWhere);
			// 1.已经存在，更新总预算为最新，更新预算剩余为 预算剩余+（最新总预算-老总预算）
			if (rt.next()) {
				new BaseBean().writeLog("更新已有预算！^--^");
				// 获取原总预算
				String oldDBBGAmount = rt.getString("dBBGAmount");
				// 获取原剩余预算
				String oldDcurrentamount = rt.getString("dcurrentamount");
				// 获取新的总预算
				String newDBBGAmount = map.get("dBBGAmount");
				// 计算总预算差和新剩余预算
				String ce = BigDecimalCalculate.floatSubtract(newDBBGAmount, oldDBBGAmount);
				String dcurrentamount_new = BigDecimalCalculate.floatAdd(oldDcurrentamount, ce);
				// 计算已用预算
				String dsumamount = BigDecimalCalculate.floatSubtract(newDBBGAmount, dcurrentamount_new);
				// 执行更新
				String updateSql = "update " + ysxxtable + " set dBBGAmount=" + newDBBGAmount + ",dsumamount="
						+ dsumamount + ",dcurrentamount=" + dcurrentamount_new + "  where " + sqlWhere;
				// 加入执行队列
				sqls.add(updateSql);
			} else {
				new BaseBean().writeLog("插入新预算！^--^");
				// 2.不存在，直接执行插入新预算语句
				String sale = "insert into uf_ystz ";
				// 获取所有字段
				Set<String> keySet = map.keySet();
				// 拼接SQL需要插入的字段
				sale += "(";
				for (String key : keySet) {
					sale += key + ",";
				}
				sale += ")";
				sale += "values(";
				// 拼接SQL需要的字段值
				for (String key : keySet) {
					String value = map.get(key);
					if (value == null || "".equals(value)) {
						sale += "\'\',";
					} else {
						sale += "\'" + value + "\',";
					}
				}
				sale += ")";
				// 删除最后一个字段的逗号
				sale = sale.replace(",)", ")");
				// 添加sql
				// sb.append(sale);
				sqls.add(sale);
			}
		}

		log.append("获取Connection.....");
		// 获取Connection
		try {
//			Connection conn = jdbcutil.getConn();
//			Statement stmt = conn.createStatement();
//			conn.setAutoCommit(false);
			for (int i = 0; i < sqls.size(); i++) {
				// 添加sql批处理
				// log.append("批处理添加SQL：" + sqls.get(i));
//				stmt.addBatch(sqls.get(i));
				new BaseBean().writeLog("批处理执行SQL：" + sqls.get(i));
				rt.execute(sqls.get(i));
				
			}
//			log.append("执行批处理SQL.....");
//			stmt.executeBatch();
//			conn.commit();
//			// 提交事务
//			log.append("提交事务.....");
//			// 关闭连接
//			stmt.close();
//			conn.close();
			log.append("同步完成.....");
			log.append("共执行："+sqls.size()+"条SQL！itemcount:"+itemcount);
		} catch (Exception e) {
			e.printStackTrace();
		}

		return log.toString();
	}

	/**
	 * 转换SQL查询结果集为list包含MAP对象
	 * 
	 * @param sql
	 * @return
	 */
	public static ArrayList<Map<String, String>> sql2map(String sql) {
		RecordSetDataSource rs = new RecordSetDataSource("ZJK");
		// 结果集
		ArrayList<Map<String, String>> list = new ArrayList<Map<String, String>>();
		rs.execute(sql);

		String iyear;
		String cbudgettype;
		String centitycode;
		String centityname;
		String cexpenseitemcode;
		String cexpenseitemname;
		String citemcode;
		String citemname;
		String dBBGAmount;
		String dsumamount;
		String dcurrentamount;
		String type;

		while (rs.next()) {
			Map<String, String> ys = new LinkedHashMap<String, String>();

			// 获取主要信息
			iyear = Util.null2String(rs.getString("iyear"));
			cbudgettype = Util.null2String(rs.getString("cbudgettype"));
			centitycode = Util.null2String(rs.getString("centitycode"));
			centityname = Util.null2String(rs.getString("centityname"));
			cexpenseitemcode = Util.null2String(rs.getString("cexpenseitemcode"));
			cexpenseitemname = Util.null2String(rs.getString("cexpenseitemname"));
			citemcode = Util.null2String(rs.getString("citemcode"));
			citemname = Util.null2String(rs.getString("citemname"));
			// 保留两位小数
			dBBGAmount = Util.null2String(rs.getString("dBBGAmount"));
			dsumamount = Util.null2String(rs.getString("dsumamount"));
			dcurrentamount = Util.null2String(rs.getString("dcurrentamount"));
			type = "";

			new BaseBean().writeLog("查询到：centitycode:"+centitycode+"  centityname:"+centityname+"  citemcode:"+citemcode);
			
			// 拼接map对象
			ys.put("iyear", iyear);
			ys.put("cbudgettype", cbudgettype);
			ys.put("centitycode", centitycode);
			ys.put("centityname", centityname);
			ys.put("cexpenseitemcode", cexpenseitemcode);
			ys.put("cexpenseitemname", cexpenseitemname);
			ys.put("citemcode", citemcode);
			ys.put("citemname", citemname);
			ys.put("dBBGAmount", dBBGAmount);
			ys.put("dsumamount", dsumamount);
			ys.put("dcurrentamount", dcurrentamount);
			// ys.put("type",type);

			list.add(ys);

		}

		// 权限重构
//		sql = "update uf_ystz set formmodeid = '26',modedatacreater = '1'";
//		RecordSet qx = new RecordSet();
//		qx.execute(sql);
//
//		// 全部权限权限重构
//		ModeRightInfo modeRightInfo = new ModeRightInfo();
//		modeRightInfo.setNewRight(true);
//		modeRightInfo.init();
//		modeRightInfo.setModeId(26);
//		modeRightInfo.resetModeRight();

		return list;
	}

}
